package com.hui.servlet;

import com.hui.service.RecodeService;
import com.hui.util.DruidUtil;
import com.hui.vo.RecodeVo;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @Description
 * @date 2023/2/11 12:42
 */
@WebServlet(urlPatterns = {"/getRecodeVoAll", "/addRecode","/getRecodeReport"})
public class RecodeServlet extends HttpServlet {

    @Override
    protected void service(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        String uri = req.getRequestURI();

        if (uri.endsWith("getRecodeVoAll")) {
            getRecodeVoAll(req, resp);
        } else if (uri.endsWith("addRecode")) {
            addRecode(req, resp);
        } else if(uri.endsWith("getRecodeReport")){
            getRecodeReport(req, resp);
        }
    }

    /**
     * 添加记录
     **/
    private void addRecode(HttpServletRequest req, HttpServletResponse resp) {


    }

    /**
     * 获得所有出库记录
     **/
    private void getRecodeVoAll(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        RecodeService service = new RecodeService();
        List<RecodeVo> list = service.findRecodeVoAll();

        req.setAttribute("list", list);
        req.getRequestDispatcher("recode_list.jsp").forward(req, resp);

    }
    private void getRecodeReport(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        //RecodeService service = new RecodeService();
        //List<RecodeVo> list = service.findRecodeVoAll();
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        List<String>catearr=new ArrayList<String>();
        List<Integer>countnum=new ArrayList<Integer>();
        List<String>catepro=new ArrayList<String>();//存库存的分类
        List<Integer>pronum=new ArrayList<Integer>();//存库存分类的产品数量
        //出库分类产品数量
        String sql="select ca.categoryName as catname,ifnull(sum(re.num),0) as total from tb_category ca \n" +
                "left join tb_product pr on ca.categoryId=pr.categoryId\n" +
                "left join record re on re.productId=pr.productId\n" +
                "left join tb_managers ma on re.mgr_id=ma.mgr_id\n" +
                "left join tb_warehouse wa on wa.warehouseId=pr.warehouseId\n" +
                "group by ca.categoryId";
        //当前库存分类产品数量
        String sqlp="select c.categoryName as caname,ifnull(sum(a.pnum),0) as canum from tb_category c left join(\n" +
                "select pr.productNum as pnum,ca.categoryId as cid from tb_product pr,tb_category ca,tb_warehouse wr\n" +
                "where pr.categoryId= ca.categoryId and pr.warehouseId =wr.warehouseId\n" +
                ") a on a.cid=c.categoryId  group by c.categoryId ";
        try {
            //创建连接
            conn = DruidUtil.getConnection();
            //创建代码块
            pstm = conn.prepareStatement(sql);

            //执行sql
            rs = pstm.executeQuery();

            while (rs.next()) {
                //   如果把一条查询到的数据，封装成一个t对象  rs
                catearr.add(rs.getString("catname"));
                countnum.add(Integer.parseInt(rs.getString("total")));
            }
            pstm = conn.prepareStatement(sqlp);
            //执行sql
            rs = pstm.executeQuery();
            while (rs.next()) {
                //   如果把一条查询到的数据，封装成一个t对象  rs
                catepro.add(rs.getString("caname"));
                pronum.add(Integer.parseInt(rs.getString("canum")));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //关闭资源
            DruidUtil.close(conn, pstm, rs);
        }
        req.setAttribute("catearr",catearr);
        req.setAttribute("countnum",countnum);
        req.setAttribute("caname",catepro);
        req.setAttribute("canum",pronum);
        req.getRequestDispatcher("recode_report.jsp").forward(req, resp);

    }
}
